﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;

using System.Text;
using KPIS.DBM;
using KPIS.GERP.ESS.MODEL;

namespace KPIS.GERP.ESS.DAL
{
    public class ActivityRequestDAL
    {
        ConvertNullable.ConvertNullable cn = new ConvertNullable.ConvertNullable();

        public DataSet GetActRequest(Nullable<int> Sequence, Nullable<int> EmployeeSequence, Nullable<int> HasGranted)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        
            DataSet ds = new DataSet();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@granted", HasGranted);
                dbManager.AddParameters(2, "@Sequence", Sequence);
                dbManager.AddParameters(3, "@EmployeeSequence", EmployeeSequence);
                dbManager.AddParameters(4, "@RefType", "HRM_AC_ACTIVITYS");
                dbManager.AddParameters(5, "@ProcessRecord", SystemInfo._ProcessRecordStatus);
                dbManager.AddParameters(6, "@ApproveRecord", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(7, "@DisApprove", SystemInfo._DisApproveRecordStatus);

                string sql = "SELECT e.first_name, e.last_name, e.name_prefix_seq"
                             + ", ar.act_request_seq, ar.act_seq, ar.emp_seq, ar.granted, ar.act_request_status"
                             + ", ar.remarks, mp.name_prefix_seq, mq.edu_qual_name, eh.edu_end_year"
                             + ", concat(mp.prefix_name,e.first_name,'  ',e.last_name) AS full_name"
                             + ", CASE WHEN ms.message_status = '0' THEN 'รอการพิจารณา'"
                             + " ELSE CASE WHEN ar.granted = '1' THEN 'อนุมัติ'"
                             + " ELSE CASE WHEN ar.granted = '2' THEN 'ไม่อนุมัติ'"
                             + " ELSE 'รอผลการพิจารณา'"
                             + " END"
                             + " END"
                             + " END AS UPPER_APPROVED_STATUS"
                             + ", s.RECORD_STATUS_NAME AS APPROVED_STATUS, ms.SEND_DATE"
                             + ", oper.BGM_PROJ_OPER_NAME, a.DATE_ACTIVITY_START, a.DATE_ACTIVITY_END, a.LOCATION_TEXT"
                             + " FROM HRM_AC_ACTIVITY_REQUESTS ar"
                             + " LEFT JOIN HRM_AC_ACTIVITYS a ON ar.ACT_SEQ = a.ACT_SEQ"
                             + " LEFT JOIN BGM_PROJECT_OPERATION oper ON a.BGM_PROJ_OPER_SEQ = oper.BGM_PROJ_OPER_SEQ"
                             + " LEFT JOIN HRM_PS_EMPLOYEES e ON ar.EMP_SEQ = e.EMP_SEQ"
                             + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                             + " LEFT JOIN HRM_MP_JOB_SITES j ON p.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                             + " LEFT JOIN MAS_NAME_PREFIXES mp ON e.NAME_PREFIX_SEQ = mp.NAME_PREFIX_SEQ"
                             + " LEFT JOIN HRM_PS_EDU_HISTORIES eh ON ar.EMP_SEQ = eh.EMP_SEQ"
                             + " LEFT JOIN MAS_EDU_QUALIFICATIONS mq ON eh.EDU_QUAL_SEQ = mq.EDU_QUAL_SEQ"
                             + " LEFT JOIN MAS_RECORD_STATUSES s ON ar.ACT_REQUEST_STATUS = s.RECORD_STATUS"
                             +
                             " LEFT JOIN MESSAGE_SEND ms ON ar.ACT_REQUEST_SEQ = ms.REF_SEQ AND ms.REF_TYPE = @RefType"
                             + " WHERE ar.RECORD_STATUS <> @delete_status "
                             + (EmployeeSequence.HasValue ? " AND ar.EMP_SEQ = @EmployeeSequence" : "")
                             +
                             " AND eh.edu_end_year =  (SELECT MAX(hrm_ps_edu_histories.edu_end_year ) FROM hrm_ps_edu_histories WHERE hrm_ps_edu_histories.emp_seq = ar.emp_seq )"
                             + (HasGranted.HasValue ? " AND ar.GRANTED = @granted" : "")
                             + (Sequence.HasValue ? " AND ar.ACT_REQUEST_SEQ = @Sequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            }
            catch
            {
            }
            finally
            {
                dbManager.Dispose();
            }
            return ds;

        }
        public int GetLastInsertActivityRequest()
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        
            string strSQL;
            DataSet ds = null;
            int nResult;

            try
            {
                dbManager.Open();

                strSQL = "select scope_identity() as act_request_seq from hrm_ac_activity_requests";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                nResult = Int32.Parse(ds.Tables[0].Rows[0]["act_request_seq"].ToString());
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return nResult;
        }

        public DataSet GetActHistory(Nullable<int> emp_seq)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        
            DataSet ds = new DataSet();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@DateNow", DateTime.Today);

                string sql = "SELECT ar.act_request_seq"
                             + " ,ar.granted"
                             + " ,a.act_seq"
                             + " ,a.act_descr"
                             + " ,a.date_activity_start"
                             + " ,a.date_activity_end"
                             + " FROM hrm_ac_activity_requests AS ar"
                             + " Inner Join hrm_ac_activitys AS a ON a.act_seq = ar.act_seq"
                             + " WHERE ar.record_status = @record_status"
                             + " AND ar.emp_seq =  @emp_seq"
                             + " AND ar.granted =  '1'"
                             + " AND a.date_activity_end <=  @DateNow";

                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
            return ds;

        }
        public DataSet GetMsgSend(int Sequence)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
                DataSet ds = new DataSet();
            try
            {
                dbManager.Open();

                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                string sql = "SELECT send.send_seq, send.send_date"
                             + ", send.ref_seq, send.ref_type, send.ref_no"
                             + ", send.send_text, send.from_emp_seq, send.to_emp_seq"
                             + ", send.from_send_seq, send.message_status"
                             +
                             ", concat(cast(user1.user_first_name as char), ' ', cast(user1.user_last_name as char)) as from_user_name"
                             +
                             ", concat(cast(user2.user_first_name as char), ' ', cast(user2.user_last_name as char)) as to_user_name"
                             + ", re.reply_seq, re.reply_type"
                             + ", CASE WHEN re.reply_type = '0' THEN 'ไม่เห็นชอบ'"
                             + " ELSE CASE WHEN re.reply_type = '1' THEN 'เห็นชอบ'"
                             + " ELSE CASE WHEN re.reply_type = '2' THEN 'ส่งต่อ'"
                             + " ELSE 'รอการพิจารณา'"
                             + " END"
                             + " END"
                             + " END AS reply_type_name"
                             + ", send.remarks, send.record_status"
                             + ", send.created_by, send.created_when, send.updated_by, send.updated_when"
                             + " FROM message_send send"
                             + " LEFT JOIN message_reply re ON send.send_seq = re.from_send_seq"
                             + " LEFT JOIN sys_sc_users user1 ON send.from_emp_seq = user1.emp_seq"
                             + " LEFT JOIN sys_sc_users user2 ON send.to_emp_seq = user2.emp_seq"
                             + " WHERE send.record_status <> @DeleteRecord"
                             + " AND send.send_seq = @Sequence";

                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            }
            catch
            {
            }
            finally
            {
                dbManager.Dispose();
            }
            return ds;
        }
        public void InsertActRequest(int act_seq, int emp_seq, Nullable<int> granted, string remarks, int created_by)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@act_seq", act_seq);
                dbManager.AddParameters(1, "@granted", granted);
                dbManager.AddParameters(2, "@act_request_status", SystemInfo._ProcessRecordStatus);
                dbManager.AddParameters(3, "@remarks", remarks);
                dbManager.AddParameters(4, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(5, "@created_by", created_by);
                dbManager.AddParameters(6, "@created_when", DateTime.Today);
                dbManager.AddParameters(7, "@emp_seq", emp_seq);

                strSQL = "INSERT INTO hrm_ac_activity_requests"
                    + "(act_seq"
                    + ", emp_seq"
                    + ", granted"
                    + ", act_request_status"
                    + ", remarks"
                    + ", record_status"
                    + ", created_by"
                    + ", created_when)"
                    + " VALUES (@act_seq"
                    + ", @emp_seq"
                    + ", @granted"
                    + ", @act_request_status"
                    + ", @remarks"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void UpdateSendStatus(int send_seq, int msg_status, int updated_by)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@send_seq", send_seq);
                dbManager.AddParameters(1, "@message_status", msg_status);
                dbManager.AddParameters(2, "@updated_by", updated_by);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                strSQL = "UPDATE message_send"
                    + " SET message_status = @message_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE send_seq = @send_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
